package srdb.migration.dbhandler.redwood;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.commons.io.FileUtils;

import srdb.migration.Utility;
import srdb.migration.common.IMTKConnection;
import srdb.migration.common.MTKConstants;
import srdb.migration.common.MTKMetaData;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.ColumnList;
import srdb.migration.schema.metadata.Constraint;
import srdb.migration.schema.metadata.DBLink;
import srdb.migration.schema.metadata.Function;
import srdb.migration.schema.metadata.Grant;
import srdb.migration.schema.metadata.Index;
import srdb.migration.schema.metadata.ObjectType;
import srdb.migration.schema.metadata.Package;
import srdb.migration.schema.metadata.Procedure;
import srdb.migration.schema.metadata.Role;
import srdb.migration.schema.metadata.Sequence;
import srdb.migration.schema.metadata.Synonym;
import srdb.migration.schema.metadata.Table;
import srdb.migration.schema.metadata.Trigger;
import srdb.migration.schema.metadata.User;
import srdb.migration.schema.metadata.View;

public class MetaData extends MTKMetaData {
    public static String SQL_STATEMENT_DELIMITER = "\n;";
    private IMTKConnection conn = null;

    private String getFuncBasedIndexColumnsStr = "SELECT column_expression column_name, column_position, table_name FROM all_ind_expressions WHERE index_owner = ? AND index_name = ? ORDER BY column_position ";

    private PreparedStatement getFuncBasedIndexColumns = null;
    private String parseTablesStr = "SELECT  table_name,num_rows FROM user_tables  ORDER BY num_rows desc";
    private String partitionTypeQuery = "SELECT partitioning_type, partition_count, partitioning_key_count, subpartitioning_type, subpartitioning_key_count FROM all_part_tables WHERE owner = ? AND table_name = ?";

    private String partitionKeyColsQuery = "SELECT column_name FROM all_part_key_columns WHERE object_type LIKE 'TABLE%' AND owner = ? AND name = ? ORDER BY column_position";

    private String subPartitionKeyColsQuery = "SELECT column_name FROM all_subpart_key_columns WHERE object_type LIKE 'TABLE%' AND owner = ? AND name = ? ORDER BY column_position";

    private String tablePartitionsQuery = "SELECT partition_name, high_value, tablespace_name FROM all_tab_partitions WHERE table_owner = ? AND table_name = ? ORDER BY partition_position";

    private String tableSubPartitionsQuery = "SELECT PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE FROM ALL_TAB_SUBPARTITIONS WHERE table_owner = ? AND table_name = ? ORDER BY subpartition_position";

    private PreparedStatement tablePartitionsPS = null;
    private PreparedStatement tableSubPartitionsPS = null;
    private PreparedStatement partitionTypePS = null;
    private PreparedStatement subPartitionTypePS = null;
    private PreparedStatement partitionKeyColsPS = null;
    private PreparedStatement subPartitionKeyColsPS = null;
    private final String SYS_ROLES_PRIVS = "'SYS','SYSTEM','OUTLN','MDSYS','CTXSYS','XDB','DBA','CONNECT','RESOURCE','CTXAPP',  'OEM_ADVISOR','OEM_MONITOR','PLUSTRACE','SCHEDULER_ADMIN',  'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','AUTHENTICATEDUSER',  'DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','HS_ADMIN_ROLE',  'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','IMP_FULL_DATABASE',  'LOGSTDBY_ADMINISTRATOR','RECOVERY_CATALOG_OWNER','XDBADMIN',  'SELECT_CATALOG_ROLE','XDBWEBSERVICES','REPADMIN','JAVASYSPRIV','MGMT_VIEW', 'OLAPSYS', 'ORDSYS', 'SYSMAN', 'WMSYS', 'PUBLIC'";

    private String userPrivQuery = "select  granted_role from dba_role_privs where  granted_role not in ('CONNECT','RESOURCE','CTXAPP',  'OEM_ADVISOR','OEM_MONITOR','PLUSTRACE','SCHEDULER_ADMIN',  'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','AUTHENTICATEDUSER',  'DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','HS_ADMIN_ROLE',  'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','IMP_FULL_DATABASE',  'LOGSTDBY_ADMINISTRATOR','RECOVERY_CATALOG_OWNER','XDBADMIN',  'SELECT_CATALOG_ROLE','XDBWEBSERVICES','ORDSYS','ORDPLUGINS',  'JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','JAVA_ADMIN','JAVA_DEPLOY',  'TIMESERIES_DEVELOPER','TIMESERIES_DBA','SNMPAGENT','OSE$HTTP$ADMIN','AURORA$ORB$UNAUTHENTICATED',  'MTSSYS','AURORA$JIS$UTILITY$','TSMSYS') and  grantee = ?";

    public MetaData(IMTKConnection conn) throws Exception {
        this.conn = conn;

        if (conn != null)
            this.dbConn = conn.getConnection();
    }

    public MetaData(IMTKConnection conn, String targetSchemaName)
            throws Exception {
        this.conn = conn;

        if (conn != null) {
            this.dbConn = conn.getConnection();
        }

        this.targetSchemaName = targetSchemaName;
    }

    protected void initSynonymStatement(String commaSepSynNames)
            throws SQLException {
        this.getSynonymStr = "SELECT synonym_name,table_name,DB_LINK,table_owner, owner from all_synonyms WHERE (owner = 'PUBLIC' OR owner= ?) AND (table_owner = ? OR (DB_LINK is not NULL and table_owner is null))";

        if (commaSepSynNames != null) {
            this.getSynonymStr = (this.getSynonymStr + " AND synonym_name IN ("
                    + commaSepSynNames + ")");
        }

        this.getSynonymStr += " ORDER BY synonym_name";
        this.getSynonyms = this.conn.getConnection().prepareStatement(
                this.getSynonymStr);
    }

    protected void initAllPublicSynonymStatement() throws SQLException {
        this.getAllPublicSynonymStr = "SELECT synonym_name,table_name,DB_LINK,table_owner, owner from all_synonyms WHERE owner = 'PUBLIC' AND (table_owner = ? OR (DB_LINK is not NULL and table_owner is null))";

        this.getAllPublicSynonymStr += " ORDER BY synonym_name";
        this.getAllPublicSynonyms = this.conn.getConnection().prepareStatement(
                this.getAllPublicSynonymStr);
    }

    protected void initAllPrivateSynonymStatement() throws SQLException {
        this.getAllPrivateSynonymStr = "SELECT synonym_name,table_name,DB_LINK,table_owner, owner from all_synonyms WHERE owner= ? AND (table_owner = ? OR (DB_LINK is not NULL and table_owner is null))";

        this.getAllPrivateSynonymStr += " ORDER BY synonym_name";
        this.getAllPrivateSynonyms = this.conn.getConnection()
                .prepareStatement(this.getAllPrivateSynonymStr);
    }

    protected void initTableStatement(String commaSepTableNames)
            throws SQLException {
        this.getTablesStr = "SELECT object_name table_name, b.comments FROM all_objects a, all_tab_comments b WHERE a.owner = ? AND object_type = 'TABLE' AND generated = 'N' AND object_name NOT IN  (SELECT table_name FROM all_snapshots WHERE owner = ?) AND a.owner = b.owner AND a.object_name = b.table_name ";

        if (commaSepTableNames != null) {
            this.getTablesStr = (this.getTablesStr + " AND object_name IN ("
                    + commaSepTableNames + ") ");
        }

        this.getTablesStr += " ORDER BY table_name ";
        this.getTables = this.conn.getConnection().prepareStatement(
                this.getTablesStr);
    }

    protected void initColumnStatement() throws SQLException {
        // this.getColumnsStr =
        // "SELECT a.data_default, a.column_name, a.data_type, a.data_length,        a.data_precision, a.data_scale, a.nullable, b.comments, a.char_used, a.char_length FROM all_tab_columns a, all_col_comments b WHERE a.owner = ? and a.table_name = ? AND a.owner = b.owner AND a.table_name = b.table_name AND a.column_name = b.column_name ";
        // oracle 8.1.7
        this.getColumnsStr = "SELECT a.data_default, a.column_name, a.data_type, a.data_length,a.data_precision, a.data_scale, a.nullable, b.comments FROM all_tab_columns a, all_col_comments b WHERE a.owner = ? and a.table_name = ? AND a.owner = b.owner AND a.table_name = b.table_name AND a.column_name = b.column_name ";

        this.getColumnsStr += " ORDER BY column_id ";
        this.getColumns = this.conn.getConnection().prepareStatement(
                this.getColumnsStr);
    }

    protected void initTriggerStatement(String commaSepTableNames)
            throws SQLException {
        this.getTriggersStr = "SELECT trigger_name, OWNER, TABLE_OWNER, trigger_type, triggering_event,        table_name, trigger_body,when_clause FROM all_triggers WHERE TABLE_OWNER = ? and trigger_type != 'INSTEAD OF' ";

        if (commaSepTableNames != null) {
            this.getTriggersStr = (this.getTriggersStr + " AND table_name IN ("
                    + commaSepTableNames + ") ");
        }

        this.getTriggers = this.conn.getConnection().prepareStatement(
                this.getTriggersStr);
    }

    protected void initViewStatement(String commaSepViewNames)
            throws SQLException {
        this.getViewsStr = "SELECT a.view_name, a.text FROM all_views a, all_objects o WHERE a.owner = ? ";

        if (commaSepViewNames != null) {
            this.getViewsStr = (this.getViewsStr + " AND a.view_name IN ("
                    + commaSepViewNames + ") ");
        }

        this.getViewsStr += " AND a.view_name = o.object_name AND o.owner = a.owner order by o.created";

        this.getViews = this.conn.getConnection().prepareStatement(
                this.getViewsStr);
    }

    protected void initSequenceStatement(String commaSepSequenceNames)
            throws SQLException {
        this.getSequencesStr = "SELECT sequence_name, min_value, max_value, increment_by,        cycle_flag, cache_size, last_number FROM all_sequences WHERE sequence_owner = ? ";

        if (commaSepSequenceNames != null) {
            this.getSequencesStr = (this.getSequencesStr
                    + " AND sequence_name IN (" + commaSepSequenceNames + ") ");
        }

        this.getSequences = this.conn.getConnection().prepareStatement(
                this.getSequencesStr);
    }

    protected void initProcStatement(String commaSepProcNames)
            throws SQLException {
        this.getProcsStr = "SELECT s.name, s.line, s.text FROM all_source s, all_objects o WHERE s.owner = ? and s.type = ? AND s.owner = o.owner AND s.type = o.object_type AND s.name = o.object_name AND o.status = 'VALID' ";

        if (commaSepProcNames != null) {
            this.getProcsStr = (this.getProcsStr + " AND s.name IN ("
                    + commaSepProcNames + ") ");
        }

        this.getProcsStr += " ORDER BY s.name, s.line ";
        this.getProcs = this.conn.getConnection().prepareStatement(
                this.getProcsStr);
    }

    protected void initPackageStatement(String commaSepPackageNames)
            throws SQLException {
        this.getPackagesStr = "SELECT s.name, s.type, s.line, s.text FROM all_source s, all_objects o WHERE s.owner = ? AND s.owner = o.owner AND s.type = o.object_type AND s.name = o.object_name AND (s.type = 'PACKAGE' or s.type = 'PACKAGE BODY') AND o.status = 'VALID' ";

        if (commaSepPackageNames != null) {
            this.getPackagesStr = (this.getPackagesStr + " AND s.name IN ("
                    + commaSepPackageNames + ") ");
        }

        this.getPackagesStr += " ORDER BY s.name, s.type, s.line ";
        this.getPackages = this.conn.getConnection().prepareStatement(
                this.getPackagesStr);
    }

    protected void initConstraintStatement(String commaSepTableNames)
            throws SQLException {
        this.getConstraintsStr = "SELECT constraint_name, constraint_type, table_name,        search_condition, r_constraint_name,delete_rule,r_owner FROM all_constraints WHERE owner = ? AND status <> 'DISABLED' AND table_name in (SELECT object_name table_name FROM all_objects WHERE owner = ? AND object_type = 'TABLE' AND table_name not in  (SELECT table_name from all_snapshots where owner = ?) AND table_name not in  (SELECT table_name from all_tables where owner = ? AND partitioned = 'YES')) ";

        this.getConstraintColumnsStr = "SELECT column_name, position, table_name FROM all_cons_columns WHERE owner = ? AND constraint_name = ? ORDER BY position ";

        if (commaSepTableNames != null) {
            this.getConstraintsStr = (this.getConstraintsStr
                    + " AND table_name IN (" + commaSepTableNames + ") ");
        }

        this.getConstraints = this.conn.getConnection().prepareStatement(
                this.getConstraintsStr);
        this.getConstraintColumns = this.conn.getConnection().prepareStatement(
                this.getConstraintColumnsStr);
    }

    protected void initIndexeStatement(String commaSepTableNames)
            throws SQLException {
        this.getIndexesStr = "SELECT s.index_name, s.table_name, s.uniqueness, s.index_type, s.table_owner FROM all_indexes s WHERE owner = ? AND table_name not in (SELECT object_name table_name FROM all_objects WHERE owner = ? AND object_type = 'TABLE' AND table_name in  (SELECT table_name from all_snapshots where owner = ?)) AND index_name not in (select constraint_name from all_constraints where table_name=s.table_name AND owner= ? AND constraint_type iN ('P','U')) ";

        this.getIndexColumnsStr = "SELECT column_name, column_position, table_name FROM all_ind_columns WHERE index_owner = ? AND index_name = ? ORDER BY column_position ";

        if (commaSepTableNames != null) {
            this.getIndexesStr = (this.getIndexesStr + " AND table_name IN ("
                    + commaSepTableNames + ") ");
        }

        this.getIndexes = this.conn.getConnection().prepareStatement(
                this.getIndexesStr);
        this.getIndexColumns = this.conn.getConnection().prepareStatement(
                this.getIndexColumnsStr);
    }

    protected void initObjectTypeStatement() throws SQLException {
        this.getObjectTypesStr = "SELECT s.name, s.line, s.text,t.typecode FROM all_source s, all_types t, all_objects o WHERE s.type = 'TYPE' AND s.owner = ? AND s.owner = t.owner AND s.name=t.type_name AND s.owner = o.owner AND s.name = o.object_name AND o.status = 'VALID' ORDER BY o.created, s.name, s.line";

        this.getObjectTypes = this.conn.getConnection().prepareStatement(
                this.getObjectTypesStr);
    }

    public void getTables(String commaSepTableNames, boolean importViewAsTable)
            throws SQLException {
        super.getTables(commaSepTableNames, importViewAsTable);

        if (importViewAsTable) {
            initViewStatement(commaSepTableNames);
            getViewsAsTables();
        }
    }

    /**
     * 解析数据库表信息，主要是将用户下所有的表扫描到文件， 并统计哪些表是空的，哪些是非空,哪些表的数据量大。
     * 都记录到文件，有助于下一步做迁移的时候参考。
     *
     * @throws SQLException
     */
    // TODO
    public void parseTables() throws SQLException {
        this.parseTables = this.conn.getConnection().prepareStatement(
                this.parseTablesStr);
        ResultSet rs = this.parseTables.executeQuery();
        StringBuffer sb = new StringBuffer();
        int i = 0;
        int totalRows = 0;
        while (rs.next()) {
            sb.append(rs.getString("table_name") + "\t" + rs.getInt("num_rows"));
            sb.append("\n");
            // System.out.println(rs.getString("table_name")+" "+rs.getInt("num_rows"));
            i++;
            totalRows = totalRows + rs.getInt("num_rows");
        }
        System.out.println("扫描完毕，共 " + i + " 表,共 " + totalRows + " 行数据");

        try {
            File newFile = new File("resource/migration/info_tables.txt");
            FileUtils.writeStringToFile(newFile, sb.toString());
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    public String getParseTablesMsg() throws SQLException{
        this.parseTables = this.conn.getConnection().prepareStatement(
                this.parseTablesStr);
        ResultSet rs = this.parseTables.executeQuery();
        StringBuffer sb = new StringBuffer();
        int i = 0;
        int totalRows = 0;
        while (rs.next()) {
            sb.append(rs.getString("table_name") + "\t" + rs.getInt("num_rows"));
            sb.append("\n");
            // System.out.println(rs.getString("table_name")+" "+rs.getInt("num_rows"));
            i++;
            totalRows = totalRows + rs.getInt("num_rows");
        }
        return "扫描完毕，共 " + i + " 表,共 " + totalRows + " 行数据";
    }

    protected void getTables() throws SQLException {
        writeLog("Getting Table Definitions");
        ResultSet rs = null;
        try {
            this.getTables.setString(1, this.schema.getName());
            this.getTables.setString(2, this.schema.getName());
            rs = this.getTables.executeQuery();

            while (rs.next()) {
                if (rs.getString("table_name").startsWith("BIN$")) {
                    continue;
                }
                Table table = getTable(rs.getString("table_name"));

                if (table != null) {
                    table.setComments(rs.getString("comments"));
                    this.schema.addTable(table);
                }
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    private void getViewsAsTables() throws SQLException {
        writeLog("Getting View Definitions");
        ResultSet rs = null;
        try {
            this.getViews.setString(1, this.schema.getName());
            rs = this.getViews.executeQuery();

            while (rs.next()) {
                if (rs.getString("view_name").startsWith("BIN$")) {
                    continue;
                }
                Table table = getTable(rs.getString("view_name"));
                if (table != null)
                    this.schema.addTable(table);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected Table getTable(String TableName) throws SQLException {
        ResultSet rs = null;
        Table table = null;
        try {
            if (this.getColumns == null) {
                initColumnStatement();
            }

            this.getColumns.setString(1, this.schema.getName());
            this.getColumns.setString(2, TableName);
            rs = this.getColumns.executeQuery();
            boolean colCaseSensitive = false;

            boolean isSimpleTableName = Utility
                    .isValidOracleIdentifier(TableName);

            boolean tabCaseSensitive = false;

            if (((tabCaseSensitive = isCaseSensitive(TableName))
                    || (!isSimpleTableName) ? 1 : 0) != 0) {
                TableName = "\"" + TableName + "\"";
            }

            if (table == null) {
                table = new Table(this.schema, TableName, this.targetSchemaName);
            }

            while (rs.next()) {
                String data_default = rs.getString("data_default");

                String colName = rs.getString("column_name");

                boolean isSimpleColumnName = Utility
                        .isValidOracleIdentifier(colName);

                if (((colCaseSensitive = isCaseSensitive(colName))
                        || (!isSimpleColumnName) ? 1 : 0) != 0) {
                    colName = "\"" + colName + "\"";
                }

                Column column = new Column(table, colName);

                column.addDetail(
                        rs.getString("data_type"),
                        // rs.getString("char_used") == null ? rs
                        // .getInt("data_length") : rs
                        // .getInt("char_length")
                        rs.getInt("data_length"),//oracle8.1.7
                        rs.getInt("data_precision"),
                        rs.getInt("data_scale"), rs.getString("nullable"),
                        colCaseSensitive, data_default,
                        rs.getString("comments"), null);

                table.addColumn(column);
            }

            loadTablePartitions(table);
        } finally {
            if (rs != null) {
                rs.close();
            }
        }

        return table;
    }

    private void loadTableSubPartitions(Table table) throws SQLException {
        if (this.subPartitionTypePS == null) {
            this.subPartitionTypePS = this.conn.getConnection()
                    .prepareStatement(this.partitionTypeQuery);
        }

        this.subPartitionTypePS.setString(1, this.schema.getName());

        this.subPartitionTypePS.setString(2, table.getName());
        ResultSet rs = this.subPartitionTypePS.executeQuery();
        try {
            if (rs.next()) {
                table.setPartitioned(true);
                String subPartitionType = rs.getString("subpartitioning_type");

                if (subPartitionType.equalsIgnoreCase("LIST"))
                    table.setSubPartitionType(MTKConstants.TablePartitionType.LIST);
                else if (subPartitionType.equalsIgnoreCase("RANGE"))
                    table.setSubPartitionType(MTKConstants.TablePartitionType.RANGE);
                else if (subPartitionType.equalsIgnoreCase("HASH")) {
                    table.setSubPartitionType(MTKConstants.TablePartitionType.HASH);
                }

                rs.close();

                if (this.subPartitionKeyColsPS == null) {
                    this.subPartitionKeyColsPS = this.conn.getConnection()
                            .prepareStatement(this.subPartitionKeyColsQuery);
                }

                this.subPartitionKeyColsPS.setString(1, this.schema.getName());

                this.subPartitionKeyColsPS.setString(2, table.getName());
                rs = this.subPartitionKeyColsPS.executeQuery();
                ColumnList tableColList = table.getColumns();
                ColumnList tablePartitionKeyColList = new ColumnList();

                while (rs.next()) {
                    tablePartitionKeyColList.add(tableColList.get(rs
                            .getString(1)));
                }

                table.setSubPartitionKeyCols(tablePartitionKeyColList);
                rs.close();

                if (this.tableSubPartitionsPS == null) {
                    this.tableSubPartitionsPS = this.conn.getConnection()
                            .prepareStatement(this.tableSubPartitionsQuery);
                }

                this.tableSubPartitionsPS.setString(1, this.schema.getName());

                this.tableSubPartitionsPS.setString(2, table.getName());
                rs = this.tableSubPartitionsPS.executeQuery();

                while (rs.next())
                    table.addSubPartition(rs.getString("SUBPARTITION_NAME"),
                            rs.getString("HIGH_VALUE"),
                            rs.getString("PARTITION_NAME"));
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    private void loadTablePartitions(Table table) throws SQLException {
        if (this.partitionTypePS == null) {
            this.partitionTypePS = this.conn.getConnection().prepareStatement(
                    this.partitionTypeQuery);
        }

        this.partitionTypePS.setString(1, this.schema.getName());

        this.partitionTypePS.setString(2, table.getName());
        ResultSet rs = this.partitionTypePS.executeQuery();
        try {
            if (rs.next()) {
                table.setPartitioned(true);
                String partitionType = rs.getString("PARTITIONING_TYPE");
                String subPartitionType = rs.getString("SUBPARTITIONING_TYPE");
                int subPartitionKeyCount = Integer.valueOf(
                        rs.getString("subpartitioning_key_count")).intValue();

                if (partitionType.equalsIgnoreCase("LIST"))
                    table.setPartitionType(MTKConstants.TablePartitionType.LIST);
                else if (partitionType.equalsIgnoreCase("RANGE"))
                    table.setPartitionType(MTKConstants.TablePartitionType.RANGE);
                else if (partitionType.equalsIgnoreCase("HASH")) {
                    table.setPartitionType(MTKConstants.TablePartitionType.HASH);
                }

                rs.close();

                if (this.partitionKeyColsPS == null) {
                    this.partitionKeyColsPS = this.conn.getConnection()
                            .prepareStatement(this.partitionKeyColsQuery);
                }

                this.partitionKeyColsPS.setString(1, this.schema.getName());

                this.partitionKeyColsPS.setString(2, table.getName());
                rs = this.partitionKeyColsPS.executeQuery();

                ColumnList tableColList = table.getColumns();
                ColumnList tablePartitionKeyColList = new ColumnList();

                while (rs.next()) {
                    tablePartitionKeyColList.add(tableColList.get(rs
                            .getString(1)));
                }

                table.setPartitionKeyCols(tablePartitionKeyColList);
                rs.close();

                if (this.tablePartitionsPS == null) {
                    this.tablePartitionsPS = this.conn.getConnection()
                            .prepareStatement(this.tablePartitionsQuery);
                }

                this.tablePartitionsPS.setString(1, this.schema.getName());

                this.tablePartitionsPS.setString(2, table.getName());
                rs = this.tablePartitionsPS.executeQuery();

                while (rs.next()) {
                    table.addPartition(rs.getString("partition_name"),
                            rs.getString("high_value"));
                }

                if ((subPartitionKeyCount > 0)
                        && (!subPartitionType.equalsIgnoreCase("NONE")))
                    loadTableSubPartitions(table);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    /**
     * 获取存储过程
     *
     * @throws SQLException
     */

    protected void getProcedures() throws SQLException {
        writeLog("Getting Procedure Definitions");
        ResultSet rs = null;
        String lastProcName = "";
        try {
            this.getProcs.setString(1, this.schema.getName());
            this.getProcs.setString(2, "PROCEDURE");
            rs = this.getProcs.executeQuery();
            Procedure proc = null;
            while (rs.next()) {
                if (rs.getString("name").startsWith("BIN$")) {
                    continue;
                }
                if (lastProcName.compareTo(rs.getString("name")) != 0) {
                    if (proc != null) {
                        this.schema.addProcedure(proc);
                    }

                    boolean procNameCaseSensitive = isCaseSensitive(rs
                            .getString("name"));
                    proc = new Procedure(rs.getString("name"), getSchema()
                            .getName(), procNameCaseSensitive);
                    lastProcName = rs.getString("name");
                }
                proc.setSQL(rs.getString("text"));
            }
            if (proc != null)
                this.schema.addProcedure(proc);
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    /**
     * 获取方法
     *
     * @throws SQLException
     */
    protected void getFunctions() throws SQLException {
        writeLog("Getting Function Definitions");
        ResultSet rs = null;
        String lastFuncName = "";
        try {
            this.getProcs.setString(1, this.schema.getName());
            this.getProcs.setString(2, "FUNCTION");
            rs = this.getProcs.executeQuery();

            Function func = null;
            while (rs.next()) {
                if (rs.getString("name").startsWith("BIN$")) {
                    continue;
                }
                if (lastFuncName.compareTo(rs.getString("name")) != 0) {
                    if (func != null) {
                        this.schema.addFunction(func);
                    }
                    func = new Function(rs.getString("name"), getSchema()
                            .getName());
                    lastFuncName = rs.getString("name");
                }
                func.setSQL(rs.getString("text"));
            }
            if (func != null)
                this.schema.addFunction(func);
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getViews() throws SQLException {
        writeLog("Getting View Definitions");
        ResultSet rs = null;
        String viewSource = null;
        try {
            this.getViews.setString(1, this.schema.getName());
            rs = this.getViews.executeQuery();

            while (rs.next()) {
                if (rs.getString("view_name").startsWith("BIN$")) {
                    continue;
                }
                View view = new View(rs.getString("view_name"), getSchema()
                        .getName());
                viewSource = rs.getString("text");

                Table viewTable = getTable(view.getName());

                if (viewTable != null) {
                    StringBuffer viewColumnList = new StringBuffer();

                    for (int i = 0; i < viewTable.getColumns().size(); i++) {
                        Column viewTableCol = viewTable.getColumns().get(i);

                        if (i > 0) {
                            viewColumnList.append(",");
                        }

                        viewColumnList.append(viewTableCol.getName());

                        if (viewTableCol.isCaseSensitive()) {
                            continue;
                        }
                        viewSource = viewSource.replaceAll(
                                "\"" + viewTableCol.getName() + "\"",
                                viewTableCol.getName());
                    }

                    view.setColumnAliasList(viewColumnList.toString());
                }

                view.setSQL(viewSource);
                if (view != null)
                    this.schema.addView(view);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getPublicSynonyms() throws SQLException {
        writeLog("Getting Public Synonym Definitions");
        ResultSet rs = null;
        try {
            this.getSynonyms.setString(1, this.schema.getName());
            this.getSynonyms.setString(2, this.schema.getName());
            rs = this.getSynonyms.executeQuery();

            while (rs.next()) {
                Synonym ps = new Synonym(getSchema().getName());
                ps.setSynname(rs.getString("synonym_name"));
                ps.setDblink(rs.getString("db_link"));
                ps.setTable_owner(rs.getString("table_owner"));
                ps.setSynowner(rs.getString("owner"));

                if (ps.getDblink() != null)
                    ps.setSynobjname(rs.getString("table_name")
                            + "@"
                            + (ps.getDblink().indexOf(".") != -1 ? ps
                            .getDblink().substring(0,
                                    ps.getDblink().indexOf(".")) : ps
                            .getDblink()));
                else {
                    ps.setSynobjname(rs.getString("table_name"));
                }

                if (ps != null)
                    this.schema.addSynonym(ps);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getAllPublicSynonyms() throws SQLException {
        writeLog("Getting all public synonym definitions...");
        ResultSet rs = null;
        try {
            this.getAllPublicSynonyms.setString(1, this.schema.getName());

            rs = this.getAllPublicSynonyms.executeQuery();

            while (rs.next()) {
                Synonym ps = new Synonym(getSchema().getName());
                ps.setSynname(rs.getString("synonym_name"));
                ps.setDblink(rs.getString("db_link"));
                ps.setTable_owner(rs.getString("table_owner"));
                ps.setSynowner(rs.getString("owner"));

                if (ps.getDblink() != null)
                    ps.setSynobjname(rs.getString("table_name")
                            + "@"
                            + (ps.getDblink().indexOf(".") != -1 ? ps
                            .getDblink().substring(0,
                                    ps.getDblink().indexOf(".")) : ps
                            .getDblink()));
                else {
                    ps.setSynobjname(rs.getString("table_name"));
                }

                if (ps != null)
                    this.schema.addSynonym(ps);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getAllPrivateSynonyms() throws SQLException {
        writeLog("Getting all private synonym definitions...");
        ResultSet rs = null;
        try {
            this.getAllPrivateSynonyms.setString(1, this.schema.getName());
            this.getAllPrivateSynonyms.setString(2, this.schema.getName());

            rs = this.getAllPrivateSynonyms.executeQuery();

            while (rs.next()) {
                Synonym ps = new Synonym(getSchema().getName());
                ps.setSynname(rs.getString("synonym_name"));
                ps.setDblink(rs.getString("db_link"));
                ps.setTable_owner(rs.getString("table_owner"));
                ps.setSynowner(rs.getString("owner"));

                if (ps.getDblink() != null)
                    ps.setSynobjname(rs.getString("table_name")
                            + "@"
                            + (ps.getDblink().indexOf(".") != -1 ? ps
                            .getDblink().substring(0,
                                    ps.getDblink().indexOf(".")) : ps
                            .getDblink()));
                else {
                    ps.setSynobjname(rs.getString("table_name"));
                }

                if (ps != null)
                    this.schema.addSynonym(ps);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getTriggers() throws SQLException {
        writeLog("Getting Trigger Definitions");
        ResultSet rs = null;
        try {
            this.getTriggers.setString(1, this.schema.getName());
            rs = this.getTriggers.executeQuery();

            while (rs.next()) {
                if (rs.getString("trigger_name").startsWith("BIN$")) {
                    continue;
                }
                String TriggerName = rs.getString("trigger_name");
                boolean isTrigCaseSensitive = false;
                boolean isSimpleTrigName = Utility
                        .isValidOracleIdentifier(TriggerName);

                if (((isTrigCaseSensitive = isCaseSensitive(TriggerName))
                        || (!isSimpleTrigName) ? 1 : 0) != 0) {
                    TriggerName = "\"" + TriggerName + "\"";
                }
                Trigger trigger = new Trigger(TriggerName);
                String trigger_body = rs.getString("trigger_body");
                trigger.setDetails(getTable(rs.getString("table_name")),
                        rs.getString("trigger_type"),
                        rs.getString("triggering_event"));

                trigger.setOwnerName(rs.getString("OWNER"));
                trigger.setTableOwnerName(rs.getString("TABLE_OWNER"));
                trigger.setBody(trigger_body);
                String whenClause = rs.getString("when_clause");
                if ((whenClause != null) && (!whenClause.trim().equals(""))) {
                    trigger.setWhenClause(true);
                }
                if (trigger != null)
                    this.schema.addTrigger(trigger);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getSequences() throws SQLException {
        writeLog("Getting Sequence Definitions");
        ResultSet rs = null;
        try {
            this.getSequences.setString(1, this.schema.getName());
            rs = this.getSequences.executeQuery();

            while (rs.next()) {
                if (rs.getString("sequence_name").startsWith("BIN$")) {
                    continue;
                }
                boolean seqNameCaseSensitive = isCaseSensitive(rs
                        .getString("sequence_name"));
                Sequence sequence = new Sequence(rs.getString("sequence_name"),
                        getSchema().getName(), seqNameCaseSensitive);
                sequence.setRange(rs.getBigDecimal("min_value").toString(), rs
                        .getBigDecimal("max_value").toString());

                sequence.setIncrement(rs.getBigDecimal("increment_by")
                        .toString());
                sequence.setLastNumber(rs.getBigDecimal("last_number")
                        .toString());
                sequence.setCacheSize(rs.getLong("cache_size"));
                sequence.setCycleFlag(rs.getString("cycle_flag"));
                if (sequence != null)
                    this.schema.addSequence(sequence);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getPackages() throws SQLException {
        writeLog("Getting Package Definitions");
        ResultSet rs = null;
        String lastPackageName = "";
        try {
            this.getPackages.setString(1, this.schema.getName());
            rs = this.getPackages.executeQuery();

            Package pack = null;
            while (rs.next()) {
                if (rs.getString("name").startsWith("BIN$")) {
                    continue;
                }
                if (lastPackageName.compareTo(rs.getString("name")) != 0) {
                    if (pack != null) {
                        this.schema.addPackage(pack);
                    }
                    pack = new Package(rs.getString("name"), getSchema()
                            .getName());
                    lastPackageName = rs.getString("name");
                }
                if (rs.getString("type").compareToIgnoreCase("PACKAGE BODY") == 0) {
                    pack.setBodySQL(rs.getString("text"));
                    continue;
                }
                pack.setHeaderSQL(rs.getString("text"));
            }

            if (pack != null)
                this.schema.addPackage(pack);
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getConstraints() throws SQLException {
        this.getConstraints.setString(1, this.schema.getName());
        this.getConstraints.setString(2, this.schema.getName());
        this.getConstraints.setString(3, this.schema.getName());
        this.getConstraints.setString(4, this.schema.getName());
        super.getConstraints();
    }

    protected void setConstraintColumnsPSParams(Constraint constraint)
            throws SQLException {
        this.getConstraintColumns.setString(1, this.schema.getName());
        this.getConstraintColumns.setString(2, constraint.getName());
    }

    protected void getIndexes() throws SQLException {
        writeLog("Getting Index Definitions");
        ResultSet rs = null;
        ResultSet irs = null;
        try {
            this.getIndexes.setString(1, this.schema.getName());
            this.getIndexes.setString(2, this.schema.getName());
            this.getIndexes.setString(3, this.schema.getName());
            this.getIndexes.setString(4, this.schema.getName());
            rs = this.getIndexes.executeQuery();

            int indexNum = 0;
            while (rs.next()) {
                if (rs.getString("table_name").startsWith("BIN$")) {
                    continue;
                }
                String IndexName = rs.getString("index_name");
                String IndexNameOriginal = IndexName;
                boolean isSimpleIndexName = Utility
                        .isValidOracleIdentifier(IndexName);
                boolean isIndexCaseSensitive = false;

                if (((isIndexCaseSensitive = isCaseSensitive(IndexName))
                        || (!isSimpleIndexName) ? 1 : 0) != 0) {
                    IndexName = "\"" + IndexName + "\"";
                }

                Index index = new Index(getTable(rs.getString("table_name")),
                        IndexName);

                index.setIndexTableOwner(rs.getString("table_owner"));
                index.setUniqueness(rs.getString("uniqueness"));
                String indexType = rs.getString("index_type");

                if (indexType.equalsIgnoreCase("FUNCTION-BASED NORMAL")) {
                    if (this.getFuncBasedIndexColumns == null) {
                        this.getFuncBasedIndexColumns = this.conn
                                .getConnection().prepareStatement(
                                        this.getFuncBasedIndexColumnsStr);
                    }

                    this.getFuncBasedIndexColumns.setString(1,
                            this.schema.getName());
                    this.getFuncBasedIndexColumns.setString(2,
                            IndexNameOriginal);
                    irs = this.getFuncBasedIndexColumns.executeQuery();
                } else {
                    this.getIndexColumns.setString(1, this.schema.getName());
                    this.getIndexColumns.setString(2, IndexNameOriginal);
                    irs = this.getIndexColumns.executeQuery();
                }

                while (irs.next()) {
                    String indCol = irs.getString("column_name");

                    if (indexType.equalsIgnoreCase("FUNCTION-BASED NORMAL")) {
                        indCol = indCol.replaceAll("\"", "");
                        indCol = "(" + indCol + ")";
                    }

                    index.addColumn(new Column(indCol));
                }

                irs.close();

                if (index != null)
                    this.schema.addIndex(index);
            }
        } finally {
            if (irs != null) {
                irs.close();
            }

            if (rs != null) {
                rs.close();
            }

            if (this.getFuncBasedIndexColumns != null) {
                this.getFuncBasedIndexColumns.close();
                this.getFuncBasedIndexColumns = null;
            }
        }
    }

    protected void getObjectTypes() throws SQLException {
        writeLog("Getting Object Type Definitions...");
        ResultSet rs = null;
        String objTypeName = null;
        String lastObjTypeName = "";
        String typeBody = null;
        try {
            this.getObjectTypes.setString(1, this.schema.getName());
            rs = this.getObjectTypes.executeQuery();

            ObjectType objType = null;
            while (rs.next()) {
                objTypeName = rs.getString("name");
                typeBody = rs.getString("text");

                if (typeBody.contains("ALTER TYPE")) {
                    continue;
                }

                if ((typeBody.contains("\""))
                        && (objTypeName.toUpperCase().equals(objTypeName))) {
                    typeBody = typeBody.replace("\"" + objTypeName + "\"",
                            objTypeName);
                }

                typeBody = typeBody.replaceAll("int\\([0-9]+\\)", "int");

                if (lastObjTypeName.compareTo(objTypeName) != 0) {
                    if (objType != null) {
                        this.schema.addObjectType(objType);
                    }

                    objType = new ObjectType(objTypeName);
                    objType.setDetails(this.schema, rs.getString("typecode"));
                    lastObjTypeName = objTypeName;
                }

                if (typeBody.contains("MODIFY ATTRIBUTE")) {
                    String attribute = typeBody.substring(
                            typeBody.indexOf("ATTRIBUTE")
                                    + "ATTRIBUTE".length(),
                            typeBody.indexOf("CASCADE"));
                    String attributeWithOutLength = attribute.substring(0,
                            attribute.indexOf("("));
                    objType.replaceBody(objType.getBody().replaceFirst(
                            attributeWithOutLength + "\\([0-9]+\\)", attribute));
                    continue;
                }

                objType.setBody(typeBody);
            }

            if (objType != null)
                this.schema.addObjectType(objType);
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    public boolean supportsSynonyms() {
        return false;
    }

    public String getVersion() throws SQLException {
        return "";
    }

    public boolean isCaseSensitive(String identifierName) {
        return (identifierName != null)
                && ((!identifierName.equals(identifierName.toUpperCase())) || (!Utility
                .isValidOracleIdentifier(identifierName)));
    }

    public void executeScript(String sql) throws SQLException {
        Statement stmt = null;
        try {
            if (sql.compareTo("") != 0) {
                stmt = this.dbConn.createStatement();

                String[] arrSQL = sql.split(SQL_STATEMENT_DELIMITER);

                for (int i = 0; i < arrSQL.length; i++)
                    stmt.execute(arrSQL[i]);
            }
        } catch (SQLException se) {
            throw se;
        } finally {
            if (stmt != null)
                stmt.close();
        }
    }

    protected void dropTableConsts(Table table) throws SQLException {
        try {
            this.schema.clearConstraintsList();

            String tableName = Utility.removeEnclosingQuotes(table
                    .getORACompatibleName());

            getConstraints("'" + tableName + "'");

            if (isCaseSensitive(tableName)) {
                tableName = "\"" + tableName + "\"";
            }

            for (int i = 0; i < this.schema.getConstraints().size(); i++) {
                Constraint constraint = this.schema.getConstraints().get(i);

                if ((constraint.getType() == 'R')
                        || (constraint.getType() == 'r')
                        || ((constraint.getSearchCondition() != null) && (constraint
                        .getSearchCondition().contains("IS NOT NULL")))) {
                    continue;
                }
                String constName = constraint.getName();

                if (isCaseSensitive(constName)) {
                    constName = "\"" + constName + "\"";
                }

                if (constraint.getType() == 'C') {
                    table.setHasCheckConst(true);
                }

                executeScript("ALTER TABLE " + tableName + " DROP CONSTRAINT "
                        + constName);
            }
        } finally {
            this.schema.clearConstraintsList();
        }
    }

    protected void dropTableIndexesDefs(Table table) throws SQLException {
        try {
            this.schema.clearIndexesList();

            String tableName = Utility.removeEnclosingQuotes(table
                    .getORACompatibleName());

            getIndexes("'" + tableName + "'");

            if (isCaseSensitive(tableName)) {
                tableName = "\"" + tableName + "\"";
            }

            for (int i = 0; i < this.schema.getIndexes().size(); i++) {
                Index index = this.schema.getIndexes().get(i);
                String indexName = index.getName();

                if (isCaseSensitive(indexName)) {
                    indexName = "\"" + indexName + "\"";
                }

                executeScript("DROP INDEX " + indexName);
            }
        } finally {
            this.schema.clearIndexesList();
        }
    }

    public void dropTableConstsAndIndexes(
            MTKConstants.DATABASE_TYPE sourceDBType, Table table)
            throws SQLException {
        dropTableConsts(table);

        dropTableIndexesDefs(table);
    }

    public String[] getSchemas() throws SQLException {
        String excludes = "'ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','RMAN','WKPROXY','WKSYS','WMSYS','WK_TEST','XDB','FLOWS_FILES','FLOWS_020100','TSMSYS'";

        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = this.conn.getConnection().createStatement();
            rs = stmt
                    .executeQuery("select username from all_users where username not in ("
                            + excludes + ") order by username");

            ArrayList list = new ArrayList();

            while (rs.next()) {
                list.add(rs.getString(1));
            }

            String[] arrayOfString = (String[]) list.toArray(new String[0]);
            return arrayOfString;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            }
        }
    }

    protected void initDBLinkStatement() throws SQLException {
        this.getDBLinksStr = "select owner, db_link, username,host from all_db_links";
        this.getDBLinks = this.dbConn.prepareStatement(this.getDBLinksStr);
    }

    protected void getDBLinks() throws SQLException {
        writeLog("Getting DBLinks Definitions");
        ResultSet rs = null;
        try {
            rs = this.getDBLinks.executeQuery();

            while (rs.next()) {
                DBLink dbLink = new DBLink();
                dbLink.setOwner(rs.getString("owner"));
                dbLink.setName(rs.getString("db_link"));
                dbLink.setUserName(rs.getString("username"));
                dbLink.setHost(rs.getString("host"));
                this.schema.addDBLink(dbLink);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    protected void getRoles() throws SQLException {
        writeLog("Getting Roles Definitions...");
        ResultSet rs = null;
        PreparedStatement psUserPrivs = null;
        ResultSet rsUserPrivs = null;
        try {
            psUserPrivs = this.conn.getConnection().prepareStatement(
                    this.userPrivQuery);
            rs = this.getRoles.executeQuery();
            Role role = null;

            while (rs.next()) {
                role = new Role();
                String roleName = rs.getString("role");
                role.setRoleName(roleName);
                String passwordRequired = rs.getString("password_required");

                if (passwordRequired != null) {
                    if (passwordRequired.equalsIgnoreCase("yes"))
                        role.setPasswordRequired(true);
                    else {
                        role.setPasswordRequired(false);
                    }

                }

                psUserPrivs.setString(1, roleName);
                rsUserPrivs = psUserPrivs.executeQuery();

                while (rsUserPrivs.next()) {
                    String grantedRole = rsUserPrivs.getString("granted_role");

                    if (grantedRole.trim().equalsIgnoreCase("dba")) {
                        role.setDba(true);
                    }
                }

                this.schema.addRole(role);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }

            if (rsUserPrivs != null) {
                rsUserPrivs.close();
            }

            if (psUserPrivs != null)
                psUserPrivs.close();
        }
    }

    protected void initRoleStatement(String commaSepRoleNames)
            throws SQLException {
        this.getRolesStr = "select role, password_required from dba_roles where role not in ('DBA','CONNECT','RESOURCE','CTXAPP', 'OEM_ADVISOR','OEM_MONITOR','PLUSTRACE','SCHEDULER_ADMIN', 'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','AUTHENTICATEDUSER', 'DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','HS_ADMIN_ROLE', 'EJBCLIENT','EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','GLOBAL_AQ_USER_ROLE','IMP_FULL_DATABASE', 'JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','JAVA_ADMIN','JAVA_DEPLOY', 'LOGSTDBY_ADMINISTRATOR','MGMT_USER','OLAP_DBA','OLAP_USER','RECOVERY_CATALOG_OWNER','XDBADMIN', 'SELECT_CATALOG_ROLE','WM_ADMIN_ROLE','XDBWEBSERVICES')";

        if (commaSepRoleNames != null) {
            this.getRolesStr = (this.getRolesStr + " AND role in ("
                    + commaSepRoleNames + ")");
        }

        this.getRoles = this.conn.getConnection().prepareStatement(
                this.getRolesStr);
    }

    protected void getUsers() throws SQLException {
        writeLog("Getting Users Definitions...");

        ResultSet rs = null;
        PreparedStatement user_privilages = null;
        ResultSet rs_priv = null;
        try {
            user_privilages = this.conn.getConnection().prepareStatement(
                    this.userPrivQuery);
            rs = this.getUsers.executeQuery();
            User user = null;

            while (rs.next()) {
                user = new User();
                String username = rs.getString("username");
                user.setName(username);
                user_privilages.setString(1, username);
                rs_priv = user_privilages.executeQuery();

                while (rs_priv.next()) {
                    String grantedRole = rs_priv.getString("granted_role");

                    if (grantedRole.trim().equalsIgnoreCase("dba")) {
                        user.setDba(true);
                    }
                }

                if (rs_priv != null) {
                    rs_priv.close();
                }

                this.schema.addUser(user);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }

            if (rs_priv != null) {
                rs_priv.close();
            }

            if (user_privilages != null)
                user_privilages.close();
        }
    }

    protected void initUserStatement(String commaSepUserNames)
            throws SQLException {
        this.getUsersStr = "select u.username,u.account_status,p.profile, p.resource_name,p.limit from dba_users u, dba_profiles p where UPPER(username) not in ('BI','DIP','IX','OE','PM','EXFSYS','SYS','SYSTEM','OUTLN', 'MDSYS','CTXSYS','XDB','ANONYMOUS','REPADMIN', 'DBSNMP','LBACSYS','MDDATA','MGMT_VIEW','OLAPSYS','ORDSYS','ORDPLUGINS', 'OWBSYS','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','JAVA_ADMIN','JAVA_DEPLOY', 'TIMESERIES_DEVELOPER','TIMESERIES_DBA','SNMPAGENT','OSE$HTTP$ADMIN', 'AURORA$ORB$UNAUTHENTICATED','MTSSYS','AURORA$JIS$UTILITY$','SH','TSMSYS','PUBLIC',  'DMSYS','SI_INFORMTN_SCHEMA','SYSMAN','WK_TEST','WKSYS','WKPROXY','WMSYS') and username not like 'FLOWS%' and p.profile = u.profile and resource_name ='PASSWORD_LIFE_TIME'";

        if (commaSepUserNames != null) {
            this.getUsersStr = (this.getUsersStr + " AND u.username in ("
                    + commaSepUserNames.toUpperCase() + ")");
        }

        this.getUsers = this.conn.getConnection().prepareStatement(
                this.getUsersStr);
    }

    protected void initGrantStatement(String commaSepUserNames)
            throws SQLException {
        this.getGrantsStr = "select GRANTEE ,OWNER,TABLE_NAME OBJECT_NAME,GRANTOR,PRIVILEGE,GRANTABLE,HIERARCHY, 'OBJECT' ROLE_TYPE from dba_tab_privs  where grantee NOT IN ('SYS','SYSTEM','OUTLN','MDSYS','CTXSYS','XDB','DBA','CONNECT','RESOURCE','CTXAPP',  'OEM_ADVISOR','OEM_MONITOR','PLUSTRACE','SCHEDULER_ADMIN',  'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','AUTHENTICATEDUSER',  'DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','HS_ADMIN_ROLE',  'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','IMP_FULL_DATABASE',  'LOGSTDBY_ADMINISTRATOR','RECOVERY_CATALOG_OWNER','XDBADMIN',  'SELECT_CATALOG_ROLE','XDBWEBSERVICES','REPADMIN','JAVASYSPRIV','MGMT_VIEW', 'OLAPSYS', 'ORDSYS', 'SYSMAN', 'WMSYS', 'PUBLIC') and privilege in ('SELECT','INSERT','UPDATE','DELETE','REFERENCES','ALL','EXECUTE')  and owner not in ('SYSMAN','OLAPSYS','WMSYS','EXFSYS','DMSYS','ORDSYS','SYS','SYSTEM','FLOWS_FILES','MDSYS','OMWB','OMWB_EMULATION','CTXSYS','XDB')  and owner not like 'FLOWS%' ";

        if (commaSepUserNames != null) {
            this.getGrantsStr = (this.getGrantsStr + " AND grantee in ("
                    + commaSepUserNames + ")");
        }

        this.getGrantsStr += " UNION SELECT GRANTEE, null OWNER, GRANTED_ROLE OBJECT_NAME,null GRANTOR , null PRIVILEGE, null GRANTABLE, null HIERARCHY, 'ROLE' ROLE_TYPE  from sys.dba_role_privs WHERE grantee NOT IN ('SYS','SYSTEM','OUTLN','MDSYS','CTXSYS','XDB','DBA','CONNECT','RESOURCE','CTXAPP',  'OEM_ADVISOR','OEM_MONITOR','PLUSTRACE','SCHEDULER_ADMIN',  'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','AUTHENTICATEDUSER',  'DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','HS_ADMIN_ROLE',  'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','IMP_FULL_DATABASE',  'LOGSTDBY_ADMINISTRATOR','RECOVERY_CATALOG_OWNER','XDBADMIN',  'SELECT_CATALOG_ROLE','XDBWEBSERVICES','REPADMIN','JAVASYSPRIV','MGMT_VIEW', 'OLAPSYS', 'ORDSYS', 'SYSMAN', 'WMSYS', 'PUBLIC') AND granted_role NOT IN ('SYS','SYSTEM','OUTLN','MDSYS','CTXSYS','XDB','DBA','CONNECT','RESOURCE','CTXAPP',  'OEM_ADVISOR','OEM_MONITOR','PLUSTRACE','SCHEDULER_ADMIN',  'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','AUTHENTICATEDUSER',  'DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','HS_ADMIN_ROLE',  'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','IMP_FULL_DATABASE',  'LOGSTDBY_ADMINISTRATOR','RECOVERY_CATALOG_OWNER','XDBADMIN',  'SELECT_CATALOG_ROLE','XDBWEBSERVICES','REPADMIN','JAVASYSPRIV','MGMT_VIEW', 'OLAPSYS', 'ORDSYS', 'SYSMAN', 'WMSYS', 'PUBLIC')";

        if (commaSepUserNames != null) {
            this.getGrantsStr = (this.getGrantsStr + " AND grantee in ("
                    + commaSepUserNames + ")");
        }

        this.getGrantsStr += " order by grantee ";

        this.getGrants = this.conn.getConnection().prepareStatement(
                this.getGrantsStr);
    }

    protected void getGrants() throws SQLException {
        writeLog("Getting Grants Definitions...");
        ResultSet rs = null;
        try {
            rs = this.getGrants.executeQuery();
            Grant grant = null;

            while (rs.next()) {
                grant = new Grant();
                grant.setGrantee(rs.getString("GRANTEE"));
                grant.setObjectName(rs.getString("OBJECT_NAME"));

                if (rs.getString("ROLE_TYPE").equalsIgnoreCase("ROLE")) {
                    grant.setPrivilegeType(Grant.PRIV_TYPE.ROLE);
                }

                if (rs.getString("ROLE_TYPE").equalsIgnoreCase("OBJECT")) {
                    grant.setGrantable(rs.getString("GRANTABLE")
                            .equalsIgnoreCase("yes"));
                    grant.setOwner(rs.getString("OWNER"));
                    grant.setGrantor(rs.getString("GRANTOR"));
                    grant.setPrivilege(rs.getString("PRIVILEGE"));
                }

                this.schema.addGrant(grant);
            }
        } finally {
            if (rs != null)
                rs.close();
        }
    }

    public int getDBServerMajorVersion() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public int getDBServerMinorVersion() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected void initRulesStatement(String commaSepRuleNames)
            throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected void getRules() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected void initGroupsStatement(String commaSepGroupNames)
            throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected void getGroups() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public String getDatabaseName() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected void initDomainsStatement(String commaSepDomainNames)
            throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected void getDomains() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected void initCustomDataTypesStatement(String commaSepTypes)
            throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected void getCustomDataTypes() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}

/*

 */